﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Acms.DatabaseTest.Helpers
{
    public class TestConnection : IDisposable
    {
        private static bool _initalised = false;

        private SqlConnection _connection;
        private SqlTransaction _transaction;

        public TestConnection()
        {
            _connection = new SqlConnection(Properties.Settings.Default.TestDBConnection);
            _connection.Open();
            _transaction = _connection.BeginTransaction();
            if (!_initalised)
            {
                _initalised = true;
            }
        }

        public static void Clean()
        {
            var conn = new TestConnection();
            conn.Execute("Delete acms.PostPositions");
            conn.Execute("Delete acms.Posts");
            conn.Execute("Delete acms.Templates");
            conn.Execute("Delete acms.Sites");
            conn.CommitTran();
        }

        public static TestConnection Begin()
        {
            return new TestConnection();
        }

        public void Execute(string sql)
        {
            Execute(sql, null);
        }

        public void Execute(string sql, ParamCollection parameters)
        {
            var cmd = new SqlCommand();
            cmd.Transaction = _transaction;
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = _connection;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }
            cmd.ExecuteNonQuery();
        }

        public List<Dictionary<string, object>> ExecuteWithResults(string sql)
        {
            return ExecuteWithResults(sql, null);
        }

        public List<Dictionary<string,object>> ExecuteWithResults(string sql, ParamCollection parameters)
        {
            var cmd = new SqlCommand();
            cmd.Transaction = _transaction;
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = _connection;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }
            return Read(cmd);
        }

        public List<Dictionary<string, object>> ExecuteProc(string name, ParamCollection parameters)
        {
            var cmd = new SqlCommand();
            cmd.Transaction = _transaction;
            cmd.Connection = _connection;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = name;

            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters.ToArray());
            }

            return Read(cmd);
        }

        private static List<Dictionary<string, object>> Read(SqlCommand cmd)
        {
            var reader = cmd.ExecuteReader();
            var retVal = new List<Dictionary<string, object>>();
            while (reader.Read())
            {
                var tmp = new Dictionary<string, object>();
                for (var i = 0; i < reader.FieldCount; i++)
                {
                    tmp.Add(reader.GetName(i), reader.IsDBNull(i) ? null : reader.GetValue(i));
                }
                retVal.Add(tmp);
            }
            reader.Close();
            return retVal;
        }

        public void CommitTran()
        {
            _transaction.Commit();
            _transaction = null;
        }

        #region IDisposable Members

        public void Dispose()
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction = null;
            }
            if (_connection != null)
            {
                if (_connection.State != System.Data.ConnectionState.Open)
                {
                    try
                    {
                        _connection.Close();
                    }
                    catch
                    {
                    }
                }
                _connection = null;
            }
        }

        #endregion
    }
}
